package com.zjs.dao.impl;

import com.zjs.bean.Student;
import com.zjs.dao.StudentDao;
import com.zjs.utils.DBUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {
    @Override
    public List<Student> getStudentList(String stuName, String stuNo, Integer sex, Integer pageIndex, Integer pageSize) {
        //分页查询sql
        StringBuffer sql = new StringBuffer("select * from student where 1=1");
        List param = new ArrayList();
        if (stuName != null && stuName.length() > 0) {
            sql.append(" and stuname like ?");
            param.add("%" + stuName + "%");
        }
        if (stuNo != null && stuNo.length() > 0) {
            sql.append(" and stuno like ?");
            param.add("%" + stuNo + "%");
        }
        if (sex != null && sex != -1) {
            sql.append(" and sex = ?");
            param.add(sex);
        }
        if (pageIndex != null && pageSize != null) {
            sql.append(" limit ?,?");
            param.add((pageIndex - 1) * pageSize);
            param.add(pageSize);
        }

        List<Student> students = null;
        try {
            students = new ArrayList<>();
            ResultSet resultSet = query(sql.toString(), param);
            while (resultSet.next()) {
                Student student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setProfession(resultSet.getString("profession"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return students;
    }

    @Override
    public Integer getStudentTotalCount(String stuName, String stuNo, Integer sex) {
        StringBuffer sql = new StringBuffer("select count(*) from student where 1=1");
        List param = new ArrayList();
        if (stuName != null && stuName.length() > 0) {
            sql.append(" and stuname like ?");
            param.add("%" + stuName + "%");
        }
        if (stuNo != null && stuNo.length() > 0) {
            sql.append(" and stuno like ?");
            param.add("%" + stuNo + "%");
        }
        if (sex != null && sex != -1) {
            sql.append(" and sex = ?");
            param.add(sex);
        }
        ResultSet resultSet = query(sql.toString(), param);
        int count = 0;
        try {
            while (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return count;
    }

    @Override
    public int add(Student student) {
        int update = 0;
        try {
            if (student != null) {
                String sql = "insert into student(stuno, stuname, gid, sex, email, phone, registered, address, politics, idnumber, profession, introduction,regdate) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
                List param = new ArrayList();
                param.add(student.getStuNo());
                param.add(student.getStuName());
                param.add(student.getGid());
                param.add(student.getSex());
                param.add(student.getEmail());
                param.add(student.getPhone());
                param.add(student.getRegistered());
                param.add(student.getAddress());
                param.add(student.getPolitics());
                param.add(student.getIdNumber());
                param.add(student.getProfession());
                param.add(student.getIntroduction());
                param.add(new Date());
                update = update(sql, param);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public int delete(String stuId) {
        String sql = "delete from student where stuid=?";
        int update = 0;
        try {
            if (stuId != null && stuId.length() > 0) {
                List param = new ArrayList();
                param.add(stuId);
                update = update(sql, param);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public Student findById(String stuId) {
        //分页查询sql
        String sql = "select * from student where stuid=?";
        List param = new ArrayList();
        param.add(stuId);
        Student student = null;
        try {
            ResultSet resultSet = query(sql.toString(), param);
            while (resultSet.next()) {
                student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setProfession(resultSet.getString("profession"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }

    @Override
    public int update(Student student) {
        int update = 0;
        try {
            if (student != null) {
                String sql = "update student set stuno=?, stuname=?, gid=?, sex=?, email=?, phone=?, registered=?, address=?, politics=?, idnumber=?, profession=?, introduction=? where stuid=?";
                List param = new ArrayList();
                param.add(student.getStuNo());
                param.add(student.getStuName());
                param.add(student.getGid());
                param.add(student.getSex());
                param.add(student.getEmail());
                param.add(student.getPhone());
                param.add(student.getRegistered());
                param.add(student.getAddress());
                param.add(student.getPolitics());
                param.add(student.getIdNumber());
                param.add(student.getProfession());
                param.add(student.getIntroduction());
                param.add(student.getStuId());
                update = update(sql, param);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }
}
